package com.excel.operations;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

public class ExcelUpdater {
	
	private Workbook workbook;
	private String excelFileName; 
	
	public ExcelUpdater(String fileName) {
		super();
		try {
			this.workbook = ExcelUtils.getWorkBook(fileName);
		} catch (Exception e) {
			System.out.println("Something is wrong. Review the below stacktrace");
			e.printStackTrace();
		}
		this.excelFileName = fileName;
	}

	public void save(){
		try {
			// Create an output stream to write the report to.
			OutputStream outputStream = new FileOutputStream(excelFileName);

			// Write the report to the output stream
			workbook.write(outputStream);

			// Finally, save the report
			outputStream.close();
		} catch (Exception e) {
			System.out.println("Something is wrong. Review the below stacktrace");
			e.printStackTrace();
		}
	}
	
	public void update(int sheetNumber, int noOfRowsToSkip,
			List<?> dataSet, Map<String, String> excelColFieldMapping, 
			List<String> columnsToUpdate) {
		
		if(columnsToUpdate==null || 
				columnsToUpdate.size()==0){
			System.out.println("No columns given for update.");
			return;
		}
		
		if(dataSet==null || 
				dataSet.size()==0){
			System.out.println("Nothing to update since the data set is empty.");
			return;
		}
		
		Sheet sheet = workbook.getSheetAt(sheetNumber-1);
		int updateDaaSetIndex=0;
		int dataSetSize = dataSet.size();
		for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
			if (i < noOfRowsToSkip) continue;
			Row row = sheet.getRow(i);
			if(updateDaaSetIndex<dataSetSize){
				Object object = dataSet.get(updateDaaSetIndex);
				try {
					updateRow(row, object, excelColFieldMapping, columnsToUpdate);
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("Unable to update excel. Review the configurations");
					break;
				}
			}else{
				break;
			}
			updateDaaSetIndex = updateDaaSetIndex + 1;
		}
	}
	
	private void updateRow(Row row, Object object, 
			Map<String, String> excelColFieldMapping, 
			List<String> fieldsToUpdate) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		
		for (String columnRef : fieldsToUpdate) {
			int columnIndexToUpdate = CellReference.convertColStringToIndex(columnRef);
			
			String beanGetterMethod = excelColFieldMapping.get(columnRef);
			
			String valueToUpdate = (String) PropertyUtils.getProperty(object, beanGetterMethod);
			
			
			writeCell(workbook, row, columnIndexToUpdate, valueToUpdate, FormatType.TEXT);
		}
		
	}
	
    private void writeCell(Workbook workbookToUpdate, Row rowToUpdate, int columnIndexToUpdate, Object newCellValue, FormatType formatType){
    	Cell cell = rowToUpdate.createCell(columnIndexToUpdate);
    	if (newCellValue == null) {
            return;
        }
    	switch (formatType) {
        case TEXT:
            cell.setCellValue(newCellValue.toString());
            break;
        case INTEGER:
            cell.setCellValue(((Number) newCellValue).intValue());
            cell.setCellStyle(getCellStyle(workbookToUpdate, "#,##0"));
            break;
        case FLOAT:
            cell.setCellValue(((Number) newCellValue).doubleValue());
            cell.setCellStyle(getCellStyle(workbookToUpdate, "#,##0.00"));
            break;
        case DATE:
            cell.setCellValue((Date) newCellValue);
            cell.setCellStyle(getCellStyle(workbookToUpdate, "m/d/yy"));
            break;
        case MONEY:
            cell.setCellValue(((Number) newCellValue).intValue());
            cell.setCellStyle(getCellStyle(workbookToUpdate, "$#,##0.00;$#,##0.00"));
            break;
        case PERCENTAGE:
            cell.setCellValue(((Number) newCellValue).doubleValue());
            cell.setCellStyle(getCellStyle(workbookToUpdate, "0.00%"));
        }
    }
    
    private CellStyle getCellStyle(Workbook workbook, String format){
		CreationHelper createHelper = workbook.getCreationHelper();
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
		return cellStyle;
	}
}
